package com.aorise.utils.excel;


import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.aorise.exception.ServiceException;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;
import java.rmi.ServerException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @Author:Shenzhiwei
 * @Desicription:ExcelUtil工具类
 * @Date:Created in 2017-08-16 16:41
 * @Modified By:
 */
public class ExcelUtil {
    public static String DEFAULT_DATE_PATTERN = "yyyy年MM月dd日";//默认日期格式
    public static int DEFAULT_COLOUMN_WIDTH = 17;


    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title       标题行
     * @param headMap     属性-列头
     * @param jsonArray   数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth    列宽 默认 至少17个字节
     */
    public static void exportExcelX(String title, Map<String, String> headMap, JSONArray jsonArray, String datePattern, int colWidth, OutputStream out) {
        if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        // 生成一个表格标题行样式
        CellStyle headerStyle = getColumnTopStyle(workbook);
        // 生成非标题样式
        CellStyle cellStyle = getColumnStyle(workbook);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(cellStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            for (int i = 0; i < properties.length; i++) {
                SXSSFCell newCell = dataRow.createCell(i);

                Object o = jo.get(properties[i]);
                String cellValue = "";
                if (o == null) cellValue = "";
                else if (o instanceof Date) cellValue = new SimpleDateFormat(datePattern).format(o);
                else if (o instanceof Float || o instanceof Double)
                    cellValue = new BigDecimal(o.toString()).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                else cellValue = o.toString();

                newCell.setCellValue(cellValue);
                newCell.setCellStyle(cellStyle);
            }
            rowIndex++;
        }

        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public static final String FILE_SEPARATOR = System.getProperties().getProperty("file.separator");


    /**
     * 导出Excel 2007 OOXML (.xlsx)格式
     *
     * @param title       标题行
     * @param headMap     属性-列头
     * @param jsonArray   数据集
     * @param datePattern 日期格式，传null值则默认 年月日
     * @param colWidth    列宽 默认 至少17个字节
     */
    public static void exportExcelAndPicture(String title, Map<String, String> headMap, JSONArray jsonArray,
                                             String datePattern, int colWidth, OutputStream out) throws Exception {
        if (datePattern == null) datePattern = DEFAULT_DATE_PATTERN;
        // 声明一个工作薄
        SXSSFWorkbook workbook = new SXSSFWorkbook(1000);//缓存
        workbook.setCompressTempFiles(true);
        // 生成一个表格标题行样式
        CellStyle headerStyle = getColumnTopStyle(workbook);
        // 生成非标题样式
        CellStyle cellStyle = getColumnStyle(workbook);
        // 生成一个(带标题)表格
        SXSSFSheet sheet = workbook.createSheet();
        //生成用于插入图片的容器--这个方法返回的类型在老api中不同
        final Drawing patriarch = sheet.createDrawingPatriarch();
        //设置列宽
        int minBytes = colWidth < DEFAULT_COLOUMN_WIDTH ? DEFAULT_COLOUMN_WIDTH : colWidth;//至少字节数
        int[] arrColWidth = new int[headMap.size()];
        // 产生表格标题行,以及设置列宽
        String[] properties = new String[headMap.size()];
        String[] headers = new String[headMap.size()];
        int ii = 0;
        for (Iterator<String> iter = headMap.keySet().iterator(); iter
                .hasNext(); ) {
            String fieldName = iter.next();

            properties[ii] = fieldName;
            headers[ii] = headMap.get(fieldName);

            int bytes = fieldName.getBytes().length;
            arrColWidth[ii] = bytes < minBytes ? minBytes : bytes;
            sheet.setColumnWidth(ii, arrColWidth[ii] * 256);
            ii++;
        }
        // 遍历集合数据，产生数据行
        int rowIndex = 0;
        for (Object obj : jsonArray) {
            if (rowIndex == 65535 || rowIndex == 0) {
                if (rowIndex != 0) sheet = workbook.createSheet();//如果数据超过了，则在第二页显示

                SXSSFRow titleRow = sheet.createRow(0);//表头 rowIndex=0
                titleRow.createCell(0).setCellValue(title);
                titleRow.getCell(0).setCellStyle(cellStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headMap.size() - 1));

                SXSSFRow headerRow = sheet.createRow(1); //列头 rowIndex =1
                for (int i = 0; i < headers.length; i++) {
                    headerRow.createCell(i).setCellValue(headers[i]);
                    headerRow.getCell(i).setCellStyle(headerStyle);
                }
                rowIndex = 2;//数据内容从 rowIndex=2开始
            }
            JSONObject jo = (JSONObject) JSONObject.toJSON(obj);
            SXSSFRow dataRow = sheet.createRow(rowIndex);
            dataRow.setHeightInPoints(20);
            dataRow.setHeight((short) (100 * 20));
            int rows = 1;
            for (int i = 0; i < properties.length; i++) {
                Object o = jo.get(properties[i]);
                if (o == null) {
                    SXSSFCell newCell = dataRow.createCell(i);

                    String cellValue = "";
                    newCell.setCellValue(cellValue);
                    newCell.setCellStyle(cellStyle);
                } else {
                    if (o instanceof URL) {
                        URL url = (URL) o;
                        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
                        //设置请求方式为"GET"
                        conn.setRequestMethod("GET");
                        //超时响应时间为5秒
                        conn.setConnectTimeout(5 * 1000);
                        //通过输入流获取图片数据
                        InputStream inStream = conn.getInputStream();
                        //创建一个Buffer字符串
                        byte[] buffer = new byte[1024];
                        //每次读取的字符串长度，如果为-1，代表全部读取完毕
                        int len = 0;
                        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                        //使用一个输入流从buffer里把数据读取出来
                        while ((len = inStream.read(buffer)) != -1) {
                            //用输出流往buffer里写入数据，中间参数代表从哪个位置开始读，len代表读取的长度
                            byteArrayOut.write(buffer, 0, len);
                        }
                        //关闭输入流
                        inStream.close();
                        byte[] data = byteArrayOut.toByteArray();
                        //设置每张图片插入位置
                        //dx1—第一个单元格中的x坐标,Dy1—第一个单元格内的Y坐标,dx2—第二个单元格中的x坐标,DY2—第二个单元格内的Y坐标
                        //col1-第一个单元格的列（基于0）,row1—第一个单元格的行（基于0）,第2列-第二个单元格的列（基于0）,row2—第二个单元格的行（基于0）
                        final XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0,
                                (short) i, rowIndex, (short) (i + 1), rowIndex + 1);
                        //参数为图片插入在表格的坐标，可以自行查看api研究参数
                        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);
                        // 插入图片
                        patriarch.createPicture(anchor, workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
                        byteArrayOut.close();
                    } else {
                        SXSSFCell newCell = dataRow.createCell(i);
                        String cellValue = o.toString();
                        newCell.setCellValue(cellValue);
                        newCell.setCellStyle(cellStyle);
                    }
                }
            }
            rowIndex++;
        }

        try {
            workbook.write(out);
            workbook.close();
            workbook.dispose();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    /**
     * 导出下载,弹出下载框
     *
     * @param response
     */
    public static void download(String title, Map<String, String> headMap, JSONArray ja, HttpServletResponse response) {
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(title, headMap, ja, null, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 清除文件
     *
     * @param docsPath 文件路径
     */
    public static void cleanFile(String docsPath) {
        File file = new File(docsPath);
        file.delete();
    }

    /**
     * 创建目录
     *
     * @param destDirName
     * @return true 存在目录  false 不存在目录
     */
    public static boolean createDir(String destDirName) {
        File dir = new File(destDirName);
        if (dir.exists()) {
            return true;
        }
        if (!destDirName.endsWith(File.separator)) {
            destDirName = destDirName + File.separator;
        }
        // 创建目录
        if (dir.mkdirs()) {
            System.out.println("创建目录" + destDirName + "成功！");
            return true;
        } else {
            System.out.println("创建目录" + destDirName + "失败！");
            return false;
        }
    }

    /*
 * 列头单元格样式
 */
    public static CellStyle getColumnTopStyle(SXSSFWorkbook workbook) {
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置字体
        Font font = workbook.createFont();
        // 设置字体颜色
        font.setColor(HSSFColor.VIOLET.index);
        // 设置字体大小
        font.setFontHeightInPoints((short) 12);
        // 字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        // 设置字体名字
        font.setFontName("Courier New");
        // 在样式用应用设置的字体;
        style.setFont(font);

        return style;
    }

    /*
     * 列数据信息单元格样式
     */
    public static CellStyle getColumnStyle(SXSSFWorkbook workbook) {
        // 设置样式;
        CellStyle style = workbook.createCellStyle();
        // 设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        // 设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        // 设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        // 设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        // 设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        // 设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        // 设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        // 设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        // 设置自动换行;
        style.setWrapText(false);
        // 设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 设置字体
        Font font = workbook.createFont();
        // 设置字体大小
        font.setFontHeightInPoints((short) 10);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        // 设置字体名字
        font.setFontName("Courier New");
        // 在样式用应用设置的字体;
        style.setFont(font);

        return style;
    }

    /**
     * @param file         模板文件
     * @param response
     * @param fileDownName 文件名称
     * @param request
     * @Author:yyp
     * @Desicription:浏览器下载Excel模板
     * @Date:Created in 13:02 2019/2/20
     * @Return:
     * @Modified By:
     */
    public static void downloadTemplate(InputStream file, HttpServletResponse response,
                                        String fileDownName,
                                        HttpServletRequest request) throws Exception {
        InputStream fis = null;
        try {
            fis = file;
            byte[] buffer = new byte[fis.available()];
            int count = 0;
            while ((count = fis.read(buffer)) > 0) {
                responseSetProperties(buffer, fileDownName, response, request);
                final OutputStream os = response.getOutputStream();
                os.write(buffer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
        }
    }

    /**
     * @param buffer       输出字节流数组
     * @param fileDownName 浏览器下载文件名
     * @param response
     * @param request
     * @Author:yyp
     * @Desicription: Excel浏览器下载头响应设置
     * @Date:Created in 13:02 2019/2/20
     * @Return:
     * @Modified By:
     */
    public static void responseSetProperties(byte[] buffer, String fileDownName,
                                             HttpServletResponse response,
                                             HttpServletRequest request) throws UnsupportedEncodingException {
        response.reset();
        response
                .setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        String utf = "UTF-8";
        response.setCharacterEncoding(utf);
        String fileNames = fileDownName;
        String agent = request.getHeader("USER-AGENT");
        response.setHeader("Pragma", "public");
        response.setHeader("Cache-Control", "max-age=30");
        String codedfilename = "";

        /** 对不同浏览器做处理设置文件名编码格式 */
        if (null != agent && -1 != agent.indexOf("MSIE")
                || null != agent && -1 != agent.indexOf("Trident")) {// 支持 IE

            String name = java.net.URLEncoder.encode(fileNames, "UTF8");

            codedfilename = name;
        } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 支持 火狐,chrome等

            codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1");
        }
        response.addHeader("Content-Disposition", "attachment; filename=\"" + codedfilename + "\"");
        response.setContentLength(buffer.length);
    }

    /**
     * @param title    导出文件名称以及标题
     * @param headMap  列信息
     * @param ja       行信息
     * @param response
     * @Author:jzwx
     * @Desicription: 浏览器下载导出Excel文件
     * @Date:Created in 8:54 2018/5/4
     * @Return:
     * @Modified By:
     */
    public static void download(String title, Map<String, String> headMap, JSONArray ja,
                                HttpServletResponse response,
                                HttpServletRequest request) throws Exception {
        BufferedOutputStream bos = null;
        BufferedInputStream bis = null;
        ServletOutputStream outputStream = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelX(title, headMap, ja, null, 0, os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);

            String fileDownName = title + ".xlsx";
            responseSetProperties(content, fileDownName, response, request);

            outputStream = response.getOutputStream();
            bis = new BufferedInputStream(is);
            bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);
            }
            outputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (bis != null) {
                bis.close();
            }
            if (bos != null) {
                bos.close();
            }
            if (outputStream != null) {
                outputStream.close();
            }
        }
    }

    /**
     * @param title    导出文件名称以及标题
     * @param headMap  列信息
     * @param ja       行信息
     * @param response
     * @Author:YYP
     * @Desicription: 浏览器下载导出Excel文件插入图片
     * @Date:Created in 8:54 2018/5/4
     * @Return:
     * @Modified By:
     */
    public static byte[] downloadFromPicture(String title, Map<String, String> headMap, JSONArray ja,
                                             HttpServletResponse response,
                                             HttpServletRequest request) throws Exception {
        BufferedOutputStream bos = null;
        BufferedInputStream bis = null;
        ServletOutputStream outputStream = null;
        byte[] content = null;
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            ExcelUtil.exportExcelAndPicture(title, headMap, ja, null, 0, os);
            content = os.toByteArray();
            //InputStream is = new ByteArrayInputStream(content);

            //String fileDownName = title + ".xlsx";
            //responseSetProperties(content, fileDownName, response, request);

//            outputStream = response.getOutputStream();
//            bis = new BufferedInputStream(is);
//            bos = new BufferedOutputStream(outputStream);
//            byte[] buff = new byte[8192];
//            int bytesRead;
//            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
//                bos.write(buff, 0, bytesRead);
//            }
//            outputStream.flush();
        } catch (Exception e) {
            return null;
        }
        return content;
    }

    /**
     * 保存临时文件文件夹
     */
    static String directoryName = "excelTemp";

    /**
     * 在Excel模板添加数据，再下载
     *
     * @param dataList    数据集合
     * @param map    附加数据集合
     * @param cloumnCount 总列数
     * @param initialRow  起始行数
     * @param fileName    下载的文件名
     * @param templateName 模板的文件名
     * @param response
     * @Date:2019-05-29
     * @Modified By: cat
     */
    public static void writeExcel(List<String[]> dataList, Map<String, String> map, int cloumnCount, int initialRow, String fileName,String templateName, HttpServletResponse response) {
        OutputStream out = null;
        String fileNameTemp = System.currentTimeMillis() + "Temp.xlsx";
        try {

            //判断文件夹存在，不存在创建
            File file = new File(directoryName);
            if (!file.exists() && !file.isDirectory()) {
                file.mkdir();
            }

            ClassPathResource classPathResource = new ClassPathResource("template/"+templateName);
            InputStream is = classPathResource.getInputStream();
            boolean iRet = copy(is, directoryName + "\\" + fileNameTemp);
            if (!iRet) {
                throw new ServiceException("复制模板文件失败");
            }

            // 读取Excel文档
            File finalXlsxFile = new File(directoryName + "\\" + fileNameTemp);

            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页
            Sheet sheet = workBook.getSheetAt(0);

            /**往Excel中写新数据*/
            for (int j = 0; j < dataList.size(); j++) {
                // 创建一行：从第二行开始，跳过属性列
                Row row = sheet.createRow(j + (initialRow - 1));
                for (int k = 0; k < cloumnCount; k++) {
                    // 在一行内循环
                    row.createCell(k).setCellValue(dataList.get(j)[k]);
                }
            }

            /**如果有附加数据写附加数据*/
            if(map != null){
                for(Map.Entry<String,String> en:map.entrySet()) {
                    String key = en.getKey();
                    String Value = en.getValue();
                    String [] keys =key.split(",");

                    Row row = sheet.getRow(Integer.parseInt(keys[0]));
                    row.getCell(Integer.parseInt(keys[1])).setCellValue(Value);
                }
            }
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out = new FileOutputStream(directoryName + "\\" + fileNameTemp);

            workBook.write(out);

            //下载文件
            downloadFile(response, directoryName + "\\" + fileNameTemp, fileName,true);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 判断Excel的版本,获取Workbook
     *
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkbok(File file) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(file);
        if (file.getName().endsWith("xls")) {     //Excel&nbsp;2003
            wb = new HSSFWorkbook(in);
        } else if (file.getName().endsWith("xlsx")) {    // Excel 2007/2010
            wb = new XSSFWorkbook(in);
        }
        return wb;
    }

    /**
     * 复制文件到路径
     *
     * @param fis      要复制的文件
     * @param filePath 路径
     * @return
     */
    public static boolean copy(InputStream fis, String filePath) {
        boolean iRet = false;
        int n = 0;
        FileOutputStream fos;
        try {
            fos = new FileOutputStream(filePath);
            while ((n = fis.read()) != -1) {
                fos.write(n);
            }
            fis.close();
            fos.close();
            iRet = true;

        } catch (FileNotFoundException e) {

            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return iRet;
    }

    /**
     * 下载文件
     *
     * @param response
     * @param filePath 文件路径
     * @param fileName 文件名
     * @param del      下载完是否删除文件
     */
    public static void downloadFile(HttpServletResponse response, String filePath, String fileName, boolean del) {
        response.reset();
        try {
            fileName = URLEncoder.encode(fileName + ".xlsx", "UTF-8");
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        //fileName = fileName + ".xlsx";
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.addHeader("Content-Disposition", "attachment; filename=" + fileName + ";filename*=utf-8''" + fileName);
        response.setCharacterEncoding("UTF-8");
        BufferedInputStream bis = null;
        OutputStream os = null;
        try {
            bis = new BufferedInputStream(new FileInputStream(filePath));
            byte[] b = new byte[bis.available() + 1000];
            int i = 0;
            os = response.getOutputStream();   //直接下载导出
            while ((i = bis.read(b)) != -1) {
                os.write(b, 0, i);
            }
            os.flush();
            bis.close();
            os.close();

            if (del) {
                //删除临时文件
                File f = new File(filePath);
                if (f.exists()) {
                    f.delete();
                }
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
